//
//  DataBaseHandler.m
//  myCar
//
//  Created by 王子洁 on 14/11/5.
//  Copyright (c) 2014年 lanou3g.com 蓝鸥科技. All rights reserved.
//

#import "DataBaseHandler.h"

@implementation DataBaseHandler

+ (DataBaseHandler *)shareInstance
{
    static DataBaseHandler *handler = nil;
    if (handler == nil) {
        handler = [[DataBaseHandler alloc] init];
        [handler openDB];
    }
    return handler;
}

- (void)openDB
{
    NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    NSString *dataBasePath = [docPath stringByAppendingPathComponent:@"dataBase.db"];
    NSLog(@"-------%@", dataBasePath);
    int result = sqlite3_open([dataBasePath UTF8String], &dbPoint);
    if (result == SQLITE_OK) {
        NSLog(@"数据库开启成功");
    } else {
        NSLog(@"失败");
    }
}

- (void)createTable
{
    NSString *sql = [NSString stringWithFormat:@"create table news (image text, title text, url text primary key)"];
    int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    } else {
        NSLog(@"创建表失败");
    }
}

- (void)insertNews:(SaveNews *)news
{
    NSString *sql = [NSString stringWithFormat:@"insert into news values('%@', '%@', '%@')", news.image, news.title, news.url];
    int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"添加成功");
    } else {
        NSLog(@"添加失败");
    }
}


// 添加论坛收藏  注释：不是帖子
- (void)insertCollection:(Base *)base
{
    NSString *sql = [NSString stringWithFormat:@"insert into collection values('%@', '%@')", base.idNum, base.name];
    int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"添加成功");
    } else {
        NSLog(@"添加失败");
    }
}

///  创建收藏 table
- (void)createCollectionTable
{
    NSString *sql = [NSString stringWithFormat:@" create table collection(id text primary key, name text)"];
    int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
        
    } else {
        NSLog(@"失败");
    }
    
}
// 删除论坛收藏
- (void)deleteCollection:(Base *)base
{
   
    NSString *sql = [NSString stringWithFormat:@"delete from collection where id = '%@'", base.idNum];
    int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    } else {
        NSLog(@"删除失败");
    }
}
// 返回所有的论坛收藏
- (NSArray *)selectCollectionAll
{
     NSMutableArray *arr = [NSMutableArray array];
    NSString *sql = [NSString stringWithFormat:@"select * from collection"];
    sqlite3_stmt *stmt = nil;
   
    int result = sqlite3_prepare_v2(dbPoint, [sql UTF8String], -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        // 如果sql检查无误, 开始执行查询, 检索数据库的数据
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            // 从替身中取出每一行数据
            // 参数1: 替身
            
            Base *base = [[Base alloc]init];
            base.idNum = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 0)];
            base.name = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
            
            
            // 将创建好的学生对象添加到数组中
            [arr addObject:base];
            [base release];
            
        }
    }
    // 将替身中的所有操作, 写入本地数据库, 销毁替身
    sqlite3_finalize(stmt);
    return arr;

    
    //return arr;
}
- (void)deleteNews:(SaveNews *)news
{
    NSString *sql = [NSString stringWithFormat:@"delete from news where url = '%@'", news.url];
    int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    } else {
        NSLog(@"删除失败");
    }
}

- (NSMutableArray *)selectAll
{
    NSMutableArray *arr = [NSMutableArray array];
    sqlite3_stmt *stmt = nil;
    NSString *sql = [NSString stringWithFormat:@"select *from news"];
    int result = sqlite3_prepare_v2(dbPoint, [sql UTF8String], -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        // 如果sql检查无误, 开始执行查询, 检索数据库的数据
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            // 从替身中取出每一行数据
            // 参数1: 替身
            
            SaveNews *news = [[SaveNews alloc] init];
            news.image = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 0)];
            news.title = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
            news.url = [NSString stringWithUTF8String:(const char *) sqlite3_column_text(stmt, 2)];
            
            // 将创建好的学生对象添加到数组中
            [arr addObject:news];
            [news release];
            
        }
    }
    // 将替身中的所有操作, 写入本地数据库, 销毁替身
    sqlite3_finalize(stmt);
    return arr;
}


- (void)createForumTable
{
    NSString *sql = [NSString stringWithFormat:@"create table forum (title text, url text primary key)"];
    int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    } else {
        NSLog(@"创建表失败");
    }
}

- (void)insertForum:(SaveNews *)forum
{
    NSString *sql = [NSString stringWithFormat:@"insert into forum values('%@', '%@')", forum.title, forum.url];
    int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"添加成功");
    } else {
        NSLog(@"添加失败");
    }

}

- (void)deleteForum:(SaveNews *)forum
{
    NSString *sql = [NSString stringWithFormat:@"delete from forum where url = '%@'", forum.url];
    int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    } else {
        NSLog(@"删除失败");
    }
}

- (NSMutableArray *)selectAllForum
{
    NSMutableArray *arr = [NSMutableArray array];
    sqlite3_stmt *stmt = nil;
    NSString *sql = [NSString stringWithFormat:@"select *from forum"];
    int result = sqlite3_prepare_v2(dbPoint, [sql UTF8String], -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        // 如果sql检查无误, 开始执行查询, 检索数据库的数据
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            // 从替身中取出每一行数据
            // 参数1: 替身
            
            SaveNews *forum = [[SaveNews alloc] init];
            forum.title = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 0)];
            forum.url = [NSString stringWithUTF8String:(const char *) sqlite3_column_text(stmt, 1)];
            
            // 将创建好的学生对象添加到数组中
            [arr addObject:forum];
            [forum release];
            
        }
    }
    // 将替身中的所有操作, 写入本地数据库, 销毁替身
    sqlite3_finalize(stmt);
    return arr;

}

- (void)createCarTable
{
    NSString *sql = [NSString stringWithFormat:@"create table car (image text, title text, url text primary key)"];
    int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    } else {
        NSLog(@"创建表失败");
    }
}

- (void)insertCar:(SaveNews *)car
{
    NSString *sql = [NSString stringWithFormat:@"insert into car values('%@', '%@', '%@')", car.image, car.title, car.url];
    int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"添加成功");
    } else {
        NSLog(@"添加失败");
    }
}

- (void)deleteCar:(SaveNews *)car
{
    NSString *sql = [NSString stringWithFormat:@"delete from car where url = '%@'", car.url];
    int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    } else {
        NSLog(@"删除失败");
    }
}

- (NSMutableArray *)selectAllCar
{
    NSMutableArray *arr = [NSMutableArray array];
    sqlite3_stmt *stmt = nil;
    NSString *sql = [NSString stringWithFormat:@"select *from car"];
    int result = sqlite3_prepare_v2(dbPoint, [sql UTF8String], -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        // 如果sql检查无误, 开始执行查询, 检索数据库的数据
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            // 从替身中取出每一行数据
            // 参数1: 替身
            
            SaveNews *car = [[SaveNews alloc] init];
            car.image = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 0)];
            car.title = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
            car.url = [NSString stringWithUTF8String:(const char *) sqlite3_column_text(stmt, 2)];
            
            // 将创建好的学生对象添加到数组中
            [arr addObject:car];
            [car release];
            
        }
    }
    // 将替身中的所有操作, 写入本地数据库, 销毁替身
    sqlite3_finalize(stmt);
    return arr;
}


@end
